/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.util.Calendar;
import java.sql.*;
import java.util.Date;

import java.text.*;
import java.util.Vector;

/**
 *
 * @author Venus
 */
public class DatBan_Model {

    public Vector LayDSDB() {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDB), MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio from datban Order by MaDB desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector ShowDatBan(int maban) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDB), MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio from datban where MaBan = " + maban + " Order by MaDB desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean CheckBan(String makv, String tenban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from ban where MaKV = " + makv + " and TenBan = " + tenban + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckDatBan(int MaBan, String ngay, String gio) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from datban where MaBan = " + MaBan + " and Ngay = '" + ngay + "' and Gio = " + gio + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckDatBanUpdate(String madb, int MaBan, String ngay, String gio) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from datban where MaBan = " + MaBan + " and Ngay = '" + ngay + "' and Gio = " + gio + " and MaDB != " + madb + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int LayMaBan(String makv, String tenban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int maht = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select MaBan from ban where  TenBan = " + tenban + " and MaKV = " + makv + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static int LayMaBanOld(int madb) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int maht = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select MaBan from datban where MaDB = " + madb + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static boolean ThemDatBan(int maban, String tenkh, String sokhach, String ngay, String gio, String sodt) {
        Connection conn = null;

        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " insert into datban(MaBan , KhachHang, SoDT, SoKhach, Ngay, Gio) "
                    + " values ( " + maban + ", '" + tenkh + "', '" + sodt + "', " + sokhach + ", '" + ngay + "', " + gio + "  ) ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean DeleteDB(String madb) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Delete from datban where MaDB = " + madb;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector Detail(String madb) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDB), MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio from datban where MaDB = " + madb + " Order by MaDB desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean UpdateReserv(String madb, int maban, String tenkh, String sokhach, String ngay, String gio, String sodt) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Update datban set MaBan = " + maban + ", KhachHang = '" + tenkh + "', SoKhach = " + sokhach + ", Ngay = '" + ngay + "', Gio = " + gio + ", SoDT = '" + sodt + "' where MaDB = " + madb + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckDate(String tungay, String tugio, String denngay, String dengio) {
        try {
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            int n = 0;
            if (!tungay.equals("") && !tugio.equals("")) {
                int giotu = Integer.parseInt(tugio);
                int gioden = Integer.parseInt(dengio);
                if (giotu >= 25 || gioden >= 25) {
                    n = n + 1;
                } else {                                        
                    Calendar c1 = Calendar.getInstance();
                    Calendar c2 = Calendar.getInstance();
                    Date date1 = fm.parse(tungay);
                    Date date2 = fm.parse(denngay);
                    c1.setTime(date1);
                    c2.setTime(date2);
                    if (c1.after(c2)) {
                        n = n + 1;
                    }
                }
            }
            if (n == 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public static String LayTenKV(int maban, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        String tenkv = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select TenKV from khuvuc A, ban B "
                    + " where A.MaKV = B.MaKV and MaBan = " + maban + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }

    public static String LayTenBan(int maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        String tenkv = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select TenBan from ban where MaBan = " + maban + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }

    public Vector SearchDB(String tungay, String tugio, String denngay, String dengio, String tenkv, String tenban, String kh, String sodt, String sokhach, int langid) {
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int gioden = Integer.parseInt(dengio);
            String rsdate = "";
            String rsgio = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngay, gio from datban ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    int gio = rss.getInt(2);
                    Date date3 = fm.parse(x);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                            rsgio = rsgio + gio + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV and C.MaNN = " + langid + " ";
            String[] arr = {tenkv, tenban, kh, sodt, sokhach};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenkv)) {
                    query = query + " and TenKV like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tenban)) {
                    query = query + " and TenBan like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(kh)) {
                    query = query + " and KhachHang like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodt)) {
                    query = query + " and SoDT = '" + arr[i] + "' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sokhach)) {
                    query = query + " and SoKhach = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String gioqry[] = rsgio.split(";");
                String x = "";
                String y = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                for (int j = 0; j < gioqry.length; j++) {
                    y = y + " " + gioqry[j] + " ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                String gioquery = y.substring(0, y.length() - 1);
                query = query + " and Ngay in (" + datequery + ") ";
                query = query + " and Gio in (" + gioquery + ") ";
            }
            query = query + " Order by MaDB desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNgayGioDB(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDB), MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio from datban Order by Ngay " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortKVDB(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV Order by TenKV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenBanDB(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV Order by TenBan " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortKHDB(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV Order by KhachHang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortDTDB(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV Order by SoDT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSoKhachDB(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV Order by SoKhach " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayGioDB(String tungay, String tugio, String denngay, String dengio, String tenkv, String tenban, String kh, String sodt, String sokhach, int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int gioden = Integer.parseInt(dengio);
            String rsdate = "";
            String rsgio = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngay, gio from datban ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    int gio = rss.getInt(2);
                    Date date3 = fm.parse(x);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                            rsgio = rsgio + gio + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV and C.MaNN = " + langid + " ";
            String[] arr = {tenkv, tenban, kh, sodt, sokhach};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenkv)) {
                    query = query + " and TenKV like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tenban)) {
                    query = query + " and TenBan like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(kh)) {
                    query = query + " and KhachHang like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodt)) {
                    query = query + " and SoDT = '" + arr[i] + "' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sokhach)) {
                    query = query + " and SoKhach = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String gioqry[] = rsgio.split(";");
                String x = "";
                String y = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                for (int j = 0; j < gioqry.length; j++) {
                    y = y + " " + gioqry[j] + " ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                String gioquery = y.substring(0, y.length() - 1);
                query = query + " and Ngay in (" + datequery + ") ";
                query = query + " and Gio in (" + gioquery + ") ";
            }
            query = query + " Order by Ngay  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchKVDB(String tungay, String tugio, String denngay, String dengio, String tenkv, String tenban, String kh, String sodt, String sokhach, int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int gioden = Integer.parseInt(dengio);
            String rsdate = "";
            String rsgio = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngay, gio from datban ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    int gio = rss.getInt(2);
                    Date date3 = fm.parse(x);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                            rsgio = rsgio + gio + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV and C.MaNN = " + langid + " ";
            String[] arr = {tenkv, tenban, kh, sodt, sokhach};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenkv)) {
                    query = query + " and TenKV like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tenban)) {
                    query = query + " and TenBan like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(kh)) {
                    query = query + " and KhachHang like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodt)) {
                    query = query + " and SoDT = '" + arr[i] + "' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sokhach)) {
                    query = query + " and SoKhach = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String gioqry[] = rsgio.split(";");
                String x = "";
                String y = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                for (int j = 0; j < gioqry.length; j++) {
                    y = y + " " + gioqry[j] + " ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                String gioquery = y.substring(0, y.length() - 1);
                query = query + " and Ngay in (" + datequery + ") ";
                query = query + " and Gio in (" + gioquery + ") ";
            }
            query = query + " Order by TenKV  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenBanDB(String tungay, String tugio, String denngay, String dengio, String tenkv, String tenban, String kh, String sodt, String sokhach, int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int gioden = Integer.parseInt(dengio);
            String rsdate = "";
            String rsgio = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngay, gio from datban ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    int gio = rss.getInt(2);
                    Date date3 = fm.parse(x);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                            rsgio = rsgio + gio + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV and C.MaNN = " + langid + " ";
            String[] arr = {tenkv, tenban, kh, sodt, sokhach};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenkv)) {
                    query = query + " and TenKV like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tenban)) {
                    query = query + " and TenBan like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(kh)) {
                    query = query + " and KhachHang like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodt)) {
                    query = query + " and SoDT = '" + arr[i] + "' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sokhach)) {
                    query = query + " and SoKhach = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String gioqry[] = rsgio.split(";");
                String x = "";
                String y = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                for (int j = 0; j < gioqry.length; j++) {
                    y = y + " " + gioqry[j] + " ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                String gioquery = y.substring(0, y.length() - 1);
                query = query + " and Ngay in (" + datequery + ") ";
                query = query + " and Gio in (" + gioquery + ") ";
            }
            query = query + " Order by TenBan  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchKHDB(String tungay, String tugio, String denngay, String dengio, String tenkv, String tenban, String kh, String sodt, String sokhach, int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int gioden = Integer.parseInt(dengio);
            String rsdate = "";
            String rsgio = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngay, gio from datban ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    int gio = rss.getInt(2);
                    Date date3 = fm.parse(x);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                            rsgio = rsgio + gio + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV and C.MaNN = " + langid + " ";
            String[] arr = {tenkv, tenban, kh, sodt, sokhach};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenkv)) {
                    query = query + " and TenKV like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tenban)) {
                    query = query + " and TenBan like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(kh)) {
                    query = query + " and KhachHang like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodt)) {
                    query = query + " and SoDT = '" + arr[i] + "' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sokhach)) {
                    query = query + " and SoKhach = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String gioqry[] = rsgio.split(";");
                String x = "";
                String y = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                for (int j = 0; j < gioqry.length; j++) {
                    y = y + " " + gioqry[j] + " ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                String gioquery = y.substring(0, y.length() - 1);
                query = query + " and Ngay in (" + datequery + ") ";
                query = query + " and Gio in (" + gioquery + ") ";
            }
            query = query + " Order by KhachHang  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchDTDB(String tungay, String tugio, String denngay, String dengio, String tenkv, String tenban, String kh, String sodt, String sokhach, int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int gioden = Integer.parseInt(dengio);
            String rsdate = "";
            String rsgio = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngay, gio from datban ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    int gio = rss.getInt(2);
                    Date date3 = fm.parse(x);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                            rsgio = rsgio + gio + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV and C.MaNN = " + langid + " ";
            String[] arr = {tenkv, tenban, kh, sodt, sokhach};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenkv)) {
                    query = query + " and TenKV like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tenban)) {
                    query = query + " and TenBan like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(kh)) {
                    query = query + " and KhachHang like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodt)) {
                    query = query + " and SoDT = '" + arr[i] + "' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sokhach)) {
                    query = query + " and SoKhach = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String gioqry[] = rsgio.split(";");
                String x = "";
                String y = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                for (int j = 0; j < gioqry.length; j++) {
                    y = y + " " + gioqry[j] + " ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                String gioquery = y.substring(0, y.length() - 1);
                query = query + " and Ngay in (" + datequery + ") ";
                query = query + " and Gio in (" + gioquery + ") ";
            }
            query = query + " Order by SoDT  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchSoKhachDB(String tungay, String tugio, String denngay, String dengio, String tenkv, String tenban, String kh, String sodt, String sokhach, int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int gioden = Integer.parseInt(dengio);
            String rsdate = "";
            String rsgio = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngay, gio from datban ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    int gio = rss.getInt(2);
                    Date date3 = fm.parse(x);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                            rsgio = rsgio + gio + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == gioden) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                            if (gio == giotu) {
                                rsdate = rsdate + x + ";";
                                rsgio = rsgio + gio + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaDB), A.MaBan, KhachHang, SoDT, SoKhach, Ngay, Gio "
                    + " from datban A, ban B, khuvuc C "
                    + " where A.MaBan = B.MaBan and B.MaKV = C.MaKV and C.MaNN = " + langid + " ";
            String[] arr = {tenkv, tenban, kh, sodt, sokhach};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenkv)) {
                    query = query + " and TenKV like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tenban)) {
                    query = query + " and TenBan like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(kh)) {
                    query = query + " and KhachHang like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodt)) {
                    query = query + " and SoDT = '" + arr[i] + "' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sokhach)) {
                    query = query + " and SoKhach = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String gioqry[] = rsgio.split(";");
                String x = "";
                String y = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                for (int j = 0; j < gioqry.length; j++) {
                    y = y + " " + gioqry[j] + " ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                String gioquery = y.substring(0, y.length() - 1);
                query = query + " and Ngay in (" + datequery + ") ";
                query = query + " and Gio in (" + gioquery + ") ";
            }
            query = query + " Order by SoKhach  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DatBan db = new Beans.DatBan();
                db.setMaDB(rss.getInt(1));
                db.setMaBan(rss.getInt(2));
                db.setKhachHang(rss.getString(3));
                db.setSoDT(rss.getString(4));
                db.setSoKhach(rss.getInt(5));
                db.setNgay(rss.getString(6));
                db.setGio(rss.getInt(7));
                v.add(db);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
}
